/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package shoppos;

import Entities.Pickup;
import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.logging.Logger;
import sun.tools.jar.Main;

public class DBPOS {

    public DBPOS() {
    }

    public static OrderItem getItemByCode(int UPC, boolean isRegisterCustomer, int storeID) {
        OrderItem obj_OrderItem = null;
        try {

            Connection con = DBAccessSingleton.getInstance().getConnection();

            ResultSet rs = DBAccessSingleton.getInstance().getStatement().executeQuery("select SI.UPC, SI.name, SI.retailPrice, SI.taxRate, S.quantity from stock_item SI INNER JOIN stock S ON SI.UPC = S.UPC INNER JOIN store ST ON S.storeID = S.storeID where SI.UPC = " + UPC + " AND ST.storeID = " + storeID);



            if (rs.next()) {
                obj_OrderItem = new OrderItem(rs.getInt(1), BigDecimal.valueOf(rs.getInt(3)), rs.getInt(5), rs.getString(2), rs.getFloat(4), 1, evaluvateDdiscount(UPC));

            } else {
                obj_OrderItem = null;
            }
            rs.close();


        } catch (SQLException ex) {
        }

        return obj_OrderItem;
    }

    private static Discount evaluvateDdiscount(int UPC) {
        Discount obj_Discount = null;
        try {

            Connection con = DBAccessSingleton.getInstance().getConnection();

            ResultSet rs = DBAccessSingleton.getInstance().getStatement().executeQuery("SELECT DR.* FROM discount D INNER JOIN discount_rule DR ON DR.ruleId = D.ruleID WHERE D.UPC = " + UPC);



            if (rs.next()) {
                obj_Discount = new Discount(rs.getInt(1), rs.getInt(2), rs.getFloat(3), rs.getInt(4), rs.getInt(5));

            } else {
                obj_Discount = null;
            }
            rs.close();

        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }

        return obj_Discount;

    }

    public static int purchaseItem(ArrayList<OrderItem> orderCart, int storeID, int terminalID, int paymentMethod) {
        int pickupNumber = 0;
        int purchaseNumber = 0;
        try {

            Connection con = DBAccessSingleton.getInstance().getConnection();
            Pickup obj_pickup = Pickup.createPickup(storeID);


            PreparedStatement ps;
            Connection con2 = DBAccessSingleton.getInstance().getConnection();
            ps = con2.prepareStatement("INSERT INTO `shop_db`.`purchase_order` (`cardNo`, `terminalId`, `Date`, `paymentMethod`, `pickUpNo`) VALUES (NULL,'" + terminalID + "', curdate(), '" + paymentMethod + "', " + obj_pickup.pickUpNo + ")");
            pickupNumber = obj_pickup.pickUpNo;

            ps.executeUpdate();

            ps = ps = con.prepareStatement("select max(PO_NO) from purchase_order");
            ResultSet rs2 = ps.executeQuery();
            if (rs2.next()) {
                purchaseNumber = rs2.getInt(1);
            }
            ps.close();

            PreparedStatement ps1 = null;
            Connection con3 = DBAccessSingleton.getInstance().getConnection();
            for (OrderItem OI : orderCart) {
                ps1 = con3.prepareStatement("INSERT INTO `shop_db`.`order_item` (`UPC`, `PO_No`, `quantity`, `sellingPrice`, `discount`) VALUES (?, ?, ?, ?, ?);");
                ps1.setInt(1, OI.getUPC());
                ps1.setInt(2, purchaseNumber);
                ps1.setInt(3, OI.getQuantity());
                ps1.setFloat(4, OI.getSellingPrice().floatValue());
                ps1.setInt(5, 0);
                ps1.executeUpdate();
            }

            ps1.close();



        } catch (SQLException ex) {
            System.out.print(ex.getMessage());
        }
        return pickupNumber;
    }

    public static ArrayList<POSCategory> getCategoryByParent(int parentID) {
        try {

            Connection con = DBAccessSingleton.getInstance().getConnection();
            ResultSet rs;
            boolean isLeaf = true;

            if (parentID == 0) {
                rs = DBAccessSingleton.getInstance().getStatement().executeQuery("SELECT * FROM `shop_db`.`category`where parentID is NULL");
                isLeaf = false;
            } else {
                rs = DBAccessSingleton.getInstance().getStatement().executeQuery("SELECT * FROM `shop_db`.`category`where parentID = " + parentID);
            }

            ArrayList<POSCategory> arrCategory = new ArrayList<POSCategory>();


            while(rs.next()){
                POSCategory ct = new POSCategory(rs.getInt(2), rs.getString(3), isLeaf);
                arrCategory.add(ct);
            }

            return arrCategory;


        } catch (SQLException ex) {
            return null;
        }
    }

    public static ResultSet getItemsWithCategory( int storeID, int categoryID) {

        try {

            Connection con = DBAccessSingleton.getInstance().getConnection();

            ResultSet rs = DBAccessSingleton.getInstance().getStatement().executeQuery("SELECT SI.UPC, SI.Name, SI.retailPrice, SI.taxRate FROM Stock_Item SI INNER JOIN stock S on S.UPC = SI.UPC INNER JOIN Store ST on ST.storeID = S.storeID WHERE SI.categoryID = " + categoryID +" AND ST.storeID = " + storeID);
//            PreparedStatement ps;
//            ps = con.prepareStatement("SELECT SI.UPC, SI.Name, SI.retailPrice, SI.taxRate FROM Stock_Item SI INNER JOIN stock S on S.UPC = SI.UPC INNER JOIN Store ST on ST.storeID = S.storeID WHERE SI.categoryID = ? AND ST.storeID = ?");
//            ps.setInt(1, categoryID);
//            ps.setInt(2, storeID);
//            ResultSet rs = ps.


            return rs;

        } catch (SQLException ex) {
            return null;
        }


    }
}
